using Microsoft.Extensions.Options;
using SPI.AgentServer.Configuration;

namespace SPI.AgentServer.Services.Security;

/// <summary>
/// SQL 验证器 - 核心安全组件
/// 负责验证 SQL 语句的安全性，防止：
/// 1. SQL 注入攻击
/// 2. 恶意数据操作（DELETE、DROP 等）
/// 3. 敏感数据泄露
/// 4. 系统表访问
/// </summary>
public class SqlValidator : ISqlValidator
{
    private readonly ISqlParser _parser;
    private readonly SecurityConfiguration _config;
    private readonly ILogger<SqlValidator> _logger;

    public SqlValidator(ISqlParser parser, IOptions<SecurityConfiguration> options, ILogger<SqlValidator> logger)
    {
        _parser = parser;
        _config = options.Value;
        _logger = logger;
    }

    /// <summary>
    /// 验证 SQL 语句的安全性
    /// </summary>
    /// <param name="sql">要验证的 SQL 语句</param>
    /// <returns>包含验证结果、错误和警告信息的对象</returns>
    public Task<SqlValidationResult> ValidateAsync(string sql)
    {
        var result = new SqlValidationResult { IsValid = true };

        // 如果配置中禁用了 SQL 验证，直接返回通过
        if (!_config.EnableSqlValidation)
        {
            return Task.FromResult(result);
        }

        try
        {
            // Parse SQL
            var parseResult = _parser.Parse(sql);
            
            if (!parseResult.Success)
            {
                result.IsValid = false;
                result.Errors.Add($"SQL parse error: {parseResult.Error}");
                return Task.FromResult(result);
            }

            // 步骤 1：检查操作类型是否允许
            // 默认只允许 SELECT 操作
            if (_config.AllowedOperations.Any() && 
                !_config.AllowedOperations.Contains(parseResult.StatementType.ToString().ToUpper()))
            {
                result.IsValid = false;
                result.Errors.Add($"Operation '{parseResult.StatementType}' is not allowed");
            }

            // 步骤 2：检查是否包含被禁止的关键字
            // 如 DROP、DELETE、UPDATE 等危险操作
            var sqlUpper = sql.ToUpper();
            foreach (var keyword in _config.BlockedKeywords)
            {
                if (sqlUpper.Contains(keyword.ToUpper()))
                {
                    result.IsValid = false;
                    result.Errors.Add($"SQL contains blocked keyword: {keyword}");
                }
            }

            // 步骤 3：检查是否访问敏感字段
            // 如 password、token、credit_card 等
            if (_config.EnableSensitiveDataMasking)
            {
                var sensitiveFields = parseResult.Columns
                    .Where(c => _config.SensitiveFields.Any(sf => 
                        c.Contains(sf, StringComparison.OrdinalIgnoreCase)))
                    .ToList();

                if (sensitiveFields.Any())
                {
                    // 标记需要数据脱敏处理
                    result.SecurityInfo.ContainsSensitiveFields = true;
                    result.SecurityInfo.SensitiveFields = sensitiveFields;
                    result.SecurityInfo.RequiresDataMasking = true;
                    result.Warnings.Add($"Query contains sensitive fields: {string.Join(", ", sensitiveFields)}");
                }
            }

            // Additional security checks
            ValidateSecurityRules(sql, parseResult, result);

            return Task.FromResult(result);
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error validating SQL");
            result.IsValid = false;
            result.Errors.Add($"Validation error: {ex.Message}");
            return Task.FromResult(result);
        }
    }

    private void ValidateSecurityRules(string sql, SqlParseResult parseResult, SqlValidationResult result)
    {
        // Check for SQL injection patterns
        var injectionPatterns = new[]
        {
            @";\s*(DROP|DELETE|UPDATE|INSERT|CREATE|ALTER|EXEC|EXECUTE)",
            @"--\s*",
            @"/\*.*\*/",
            @"xp_\w+",
            @"sp_\w+",
            @"UNION\s+ALL\s+SELECT",
            @"UNION\s+SELECT"
        };

        foreach (var pattern in injectionPatterns)
        {
            if (System.Text.RegularExpressions.Regex.IsMatch(sql, pattern, 
                System.Text.RegularExpressions.RegexOptions.IgnoreCase))
            {
                result.Warnings.Add($"Potential SQL injection pattern detected: {pattern}");
            }
        }

        // Check for system tables/views
        var systemTables = new[] { "sys.", "INFORMATION_SCHEMA.", "master.", "msdb.", "tempdb." };
        foreach (var table in parseResult.Tables)
        {
            if (systemTables.Any(st => table.StartsWith(st, StringComparison.OrdinalIgnoreCase)))
            {
                result.Warnings.Add($"Query accesses system table: {table}");
            }
        }

        // Check for wildcard selection with sensitive tables
        if (sql.Contains("SELECT *", StringComparison.OrdinalIgnoreCase))
        {
            result.Warnings.Add("Avoid using SELECT * in production queries");
        }
    }
}
